package cn.liyunqi.ttv.main.localdb;

import cn.liyunqi.core.util.UUIDUtil;
import cn.liyunqi.ttv.main.util.StringUtil;
import cn.liyunqi.ttv.main.util.TimeUtil;
import cn.liyunqi.ttv.main.vo.UserPreference;
import cn.liyunqi.ttv.main.vo.UserTextHistory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * XML based config file help class
 * <p>
 * Created by Owen on 6/16/16.
 */
public class ConfigHelper {

    public static final String LOGIN_ID = "loginId";
    public static final String PASSWORD = "password";
    public static final String IS_SAVE_PASSWORD = "isSavePassword";

    public static final String URL = "url";

    private static final Logger _LOG = LoggerFactory.getLogger(ConfigHelper.class);
    private static final String BASE_DIR = "config";
    private static final String CONFIG_FILE = "/sqlite3.db";



    public static void createKedaxunfeiFile() throws Exception {

        File libmsc32 = new File( "libmsc32.so");
        if (!libmsc32.exists()) {
            createDLLFile(libmsc32,"libmsc32.so");
        }
        File libmsc64 = new File( "libmsc64.so");
        if (!libmsc64.exists()) {
            createDLLFile(libmsc64,"libmsc64.so");
        }
        File msc32 = new File( "msc32.dll");
        if (!msc32.exists()) {
            createDLLFile(msc32,"msc32.dll");
        }
        File msc64 = new File( "msc64.dll");
        if (!msc64.exists()) {
            createDLLFile(msc64,"msc64.dll");
        }
    }

    public static void createEmptyFiles() throws Exception {
        File file = new File(BASE_DIR);
        if (!file.exists()) {
            file.mkdir();
        }
        File uiConfigFile = new File(BASE_DIR + CONFIG_FILE);
        if (!uiConfigFile.exists()) {
            createEmptyXMLFile(uiConfigFile);
        }
    }
    public static void createDLLFile(File dllFile,String fileName)throws IOException  {
        InputStream fis = null;
        FileOutputStream fos = null;
        try {
            fis = Thread.currentThread().getContextClassLoader().getResourceAsStream("dll/" + fileName);
            fos = new FileOutputStream(dllFile);
            byte[] buffer = new byte[1024];
            int byteread = 0;
            while ((byteread = fis.read(buffer)) != -1) {
                fos.write(buffer, 0, byteread);
            }
        } finally {
            if (fis != null) fis.close();
            if (fos != null) fos.close();
        }
    }

    static void createEmptyXMLFile(File uiConfigFile) throws IOException {
        InputStream fis = null;
        FileOutputStream fos = null;
        try {
            fis = Thread.currentThread().getContextClassLoader().getResourceAsStream("db/sqlite3.db");
            fos = new FileOutputStream(uiConfigFile);
            byte[] buffer = new byte[1024];
            int byteread = 0;
            while ((byteread = fis.read(buffer)) != -1) {
                fos.write(buffer, 0, byteread);
            }
        } finally {
            if (fis != null) fis.close();
            if (fos != null) fos.close();
        }

    }
    public static String getloadValue(String name) throws Exception {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = ConnectionManager.getConnection();

            stat = conn.prepareStatement("SELECT * from dbs where name = ?");
            stat.setString(1,name);

            rs = stat.executeQuery();
            while (rs.next()) {
                String value = rs.getString("value");
                return value;
            }
            return null;
        } finally {
            if (rs != null) rs.close();
            if (stat != null) stat.close();
            if (conn != null) conn.close();
        }
    }

   public static void updateValueByName(String name,String value) throws Exception {
       Connection conn = null;
       PreparedStatement stat1 = null;
       PreparedStatement stat2 = null;
       PreparedStatement stat3 = null;
       ResultSet rs = null;
       try {
           conn = ConnectionManager.getConnection();
           stat1 = conn.prepareStatement("SELECT * from dbs where name = ?");
           stat1.setString(1,name);
           rs = stat1.executeQuery();
           if (rs.next()) {

               stat2 = conn.prepareStatement("UPDATE dbs SET value = ? where name = ?");
               stat2.setString(1,value);
               stat2.setString(2,name);
               stat2.executeUpdate();
               return;
           }
           stat3 = conn.prepareStatement("INSERT INTO dbs values(?, ?)");
           stat3.setString(1,name);
           stat3.setString(2,value);
           stat3.executeUpdate();
       } finally {
           if (rs != null) rs.close();
           if (stat1 != null) stat1.close();
           if (stat2 != null) stat2.close();
           if (stat3 != null) stat3.close();
           if (conn != null) conn.close();
       }
   }

    public static UserPreference getUserPreferenceByUserId(String userId) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        UserPreference userPreference =null;
        try {

            conn = ConnectionManager.getConnection();

            stat = conn.prepareStatement("SELECT * from local_user_info where user_id = ?");
            stat.setString(1,userId);

            rs = stat.executeQuery();
            if (rs.next()) {
                userPreference = new UserPreference();
                userPreference.setFileName(rs.getString("file_name"));
                userPreference.setFilePath(rs.getString("file_path"));
                userPreference.setPerId(rs.getString("per_id"));
                userPreference.setPit(rs.getInt("pit"));
                userPreference.setSpd(rs.getInt("spd"));
                userPreference.setVol(rs.getInt("vol"));
                userPreference.setSimplifiedFlg(rs.getInt("simplified"));
                userPreference.setTraditionalFlg(rs.getInt("traditional"));
                userPreference.setAutoDelHistory(rs.getInt("auto_del"));
                userPreference.setAutoSaveHistory(rs.getInt("auto_save"));

                return userPreference;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return userPreference;
        }


    }

    public static UserTextHistory insertHistory(String userId, String textValue, String path, String fileName, int spd, int pit, int vol, String perid, int simplified, int traditional, Long create_time) {
        Connection conn = null;
        PreparedStatement stat1 = null;

        try {

            UserTextHistory history = new UserTextHistory();
            history.setTextId(UUIDUtil.getUUID());
            history.setUserId(userId);
            history.setTextValue(textValue);
            history.setTextSimValue(textValue.length() > 16 ?textValue.substring(0,15) + "...":textValue);
            history.setFilePath(path);
            history.setFileName(fileName);
            history.setSpd(spd);
            history.setPit(pit);
            history.setVol(vol);
            history.setPerId(perid);
            history.setSimplifiedFlg(simplified);
            history.setTraditionalFlg(traditional);
            history.setCreateTime(create_time);
            history.setCreateDate(TimeUtil.formatToStringDate(create_time));

            conn = ConnectionManager.getConnection();
            stat1 = conn.prepareStatement("INSERT INTO user_text_history(text_id,user_id,text_value,text_sim_value,file_path,file_name,spd,pit,vol,per_id,simplified,traditional,create_time)values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
            stat1.setString(1,history.getTextId());
            stat1.setString(2,history.getUserId());
            //stat1.setBlob(3, textValue);
            stat1.setString(3,history.getTextValue());
            stat1.setString(4,history.getTextSimValue());
            stat1.setString(5,history.getFilePath());
            stat1.setString(6,history.getFileName());
            stat1.setInt(7,history.getSpd());
            stat1.setInt(8,history.getPit());
            stat1.setInt(9,history.getVol());
            stat1.setString(10,history.getPerId());
            stat1.setInt(11,history.getSimplifiedFlg());
            stat1.setInt(12,history.getTraditionalFlg());
            stat1.setLong(13,history.getCreateTime() );

            stat1.executeUpdate();

            return history;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            if (stat1 != null) {
                try {
                    stat1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static List<UserTextHistory> getTextHistoryByUserId(String userId){
        List<UserTextHistory> list = new ArrayList<UserTextHistory>();

        Connection conn = null;
        PreparedStatement stat = null;

        ResultSet rs = null;
        try {
            conn = ConnectionManager.getConnection();
            stat = conn.prepareStatement("SELECT text_id,text_sim_value , create_time from user_text_history where user_id = ? order by create_time desc");
            stat.setString(1,userId);
            rs = stat.executeQuery();
            while (rs.next()) {
                UserTextHistory his = new UserTextHistory();
                if(!StringUtil.isNullOrEmpty(rs.getString("text_id"))){
                    his.setTextId(rs.getString("text_id"));
                    his.setTextSimValue(rs.getString("text_sim_value"));
                    his.setCreateTime(rs.getLong("create_time"));
                    his.setCreateDate(TimeUtil.formatToStringDate(rs.getLong("create_time")));
                    list.add(his);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    public static void insertOrUpdateUserPreference(String userId, String path, String fileName, int spd, int pit, int vol, String perId, int simplified, int traditional) {
        Connection conn = null;
        PreparedStatement stat1 = null;
        PreparedStatement stat2 = null;
        PreparedStatement stat3 = null;
        ResultSet rs = null;
        try {
            conn = ConnectionManager.getConnection();
            stat1 = conn.prepareStatement("SELECT * from local_user_info where user_id = ?");
            stat1.setString(1,userId);
            rs = stat1.executeQuery();
            if (rs.next()) {

                stat2 = conn.prepareStatement("UPDATE local_user_info SET file_path = ?,file_name = ?,spd = ? ,pit = ? ,vol = ?,per_id = ? ,simplified = ? , traditional = ? where user_id = ?");
                stat2.setString(1,path);
                stat2.setString(2,fileName);
                stat2.setInt(3,spd);
                stat2.setInt(4,pit);
                stat2.setInt(5,vol);
                stat2.setString(6,perId);
                stat2.setInt(7,simplified);
                stat2.setInt(8,traditional);
                stat2.setString(9,userId);
                stat2.executeUpdate();
                return;
            }
            stat3 = conn.prepareStatement("INSERT INTO local_user_info (user_id,file_path,file_name,spd,pit,vol,per_id,simplified,traditional) values(?, ?,?,?,?,?,?,?,?)");
            stat3.setString(1,userId);
            stat3.setString(2,path);
            stat3.setString(3,fileName);
            stat3.setInt(4,spd);
            stat3.setInt(5,pit);
            stat3.setInt(6,vol);
            stat3.setString(7,perId);
            stat3.setInt(8,simplified);
            stat3.setInt(9,traditional);
            stat3.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat1 != null) {
                try {
                    stat1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat2 != null) {
                try {
                    stat2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat3 != null) {
                try {
                    stat3.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void insertOrUpdateUserPreferenceSingleByUserId(String userId,  String key, String value) {
        Connection conn = null;
        PreparedStatement stat1 = null;
        PreparedStatement stat2 = null;
        PreparedStatement stat3 = null;
        ResultSet rs = null;
        try {
            conn = ConnectionManager.getConnection();
            stat1 = conn.prepareStatement("SELECT * from local_user_info where user_id = ?");
            stat1.setString(1,userId);
            rs = stat1.executeQuery();
            if (rs.next()) {

                stat2 = conn.prepareStatement("UPDATE local_user_info SET "+ key +" = ? where user_id = ?");
                stat2.setString(1,value);
                stat2.setString(2,userId);
                stat2.executeUpdate();
                return;
            }
            stat3 = conn.prepareStatement("INSERT INTO local_user_info (user_id,"+key+") values(?, ?)");
            stat3.setString(1,userId);
            stat3.setString(2,value);
            stat3.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat1 != null) {
                try {
                    stat1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat2 != null) {
                try {
                    stat2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat3 != null) {
                try {
                    stat3.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static UserTextHistory getTextHistoryByTextId(String textId) {
        Connection conn = null;
        PreparedStatement stat = null;

        ResultSet rs = null;
        try {
            conn = ConnectionManager.getConnection();
            stat = conn.prepareStatement("SELECT * from user_text_history where text_id = ? ");
            stat.setString(1,textId);
            rs = stat.executeQuery();
            if (rs.next()) {
                UserTextHistory his = new UserTextHistory();
                if(!StringUtil.isNullOrEmpty(rs.getString("text_id"))){
                    his.setTextId(rs.getString("text_id"));
                    his.setTextSimValue(rs.getString("text_sim_value"));
                    his.setTextValue(rs.getString("text_value"));
                    his.setCreateTime(rs.getLong("create_time"));
                    his.setCreateDate(TimeUtil.formatToStringDate(rs.getLong("create_time")));

                    his.setUserId(rs.getString("user_id"));
                    his.setFilePath(rs.getString("file_path"));
                    his.setFileName(rs.getString("file_name"));
                    his.setPit(rs.getInt("pit"));
                    his.setVol(rs.getInt("vol"));
                    his.setSpd(rs.getInt("spd"));
                    his.setPerId(rs.getString("per_id"));
                    his.setSimplifiedFlg(rs.getInt("simplified"));
                    his.setTraditionalFlg(rs.getInt("traditional"));

                    return his;
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static void deleteHistoryByUserId(String userId) {
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = ConnectionManager.getConnection();
            stat = conn.prepareStatement("delete from user_text_history where user_id = ? ");
            stat.setString(1,userId);
            stat.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void deleteHistoryByUserIdAndTime(String userId, long time) {
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = ConnectionManager.getConnection();
            stat = conn.prepareStatement("delete from user_text_history where user_id = ? and create_time < ?");
            stat.setString(1,userId);
            stat.setLong(2,time);
            //stat.execute();
            int res = stat.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
